package yongxin.controller.kingdeeReport;

import java.io.OutputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.servlet.ModelAndView;

import org.jeecgframework.core.common.controller.BaseController;
import org.jeecgframework.core.common.model.json.AjaxJson;
import org.jeecgframework.core.common.model.json.DataGrid;
import org.jeecgframework.core.util.StringUtil;
import org.jeecgframework.tag.core.easyui.TagUtil;
import org.jeecgframework.tag.vo.datatable.SortDirection;
import org.jeecgframework.web.system.pojo.base.TSUser;
import org.jeecgframework.web.system.service.SystemService;

import yongxin.entity.annealer.AnnealerEntity;
import yongxin.entity.check.ContractEntity;
import yongxin.entity.check.ContractProcutEntity;
import yongxin.entity.processreport.ProcessReportEntity;
import yongxin.entity.temp_record.TempRecordEntity;
import yongxin.utils.ExcelUtils;

/**
 * @Title: Controller
 * @Description: 销售
 * @author zhangdaihao
 * @date 2015-02-05 14:03:13
 * @version V1.0
 * 
 */
@Controller
@RequestMapping("/saleController")
public class SaleController extends BaseController
{
	/**
	 * Logger for this class
	 */
	private static final Logger logger = Logger.getLogger(SaleController.class);

	@Autowired
	private SystemService systemService;

	private String message;

	public String getMessage()
	{
		return message;
	}

	public void setMessage(String message)
	{
		this.message = message;
	}

	/**
	 * 
	 * 销售明细(销售增值税发票)  依客户
	 * 
	 * @return
	 */
	@RequestMapping(params = "saleDetailList")
	public ModelAndView saleDetailList(HttpServletRequest request)
	{

		return new ModelAndView("yongxin/kingdeeReport/saleDetailList");

	}

	/**
	 * easyui 销售明细
	 * 
	 * @param request
	 * @param response
	 * @param dataGrid
	 */

	@RequestMapping(params = "datagrid")
	public void datagrid(HttpServletRequest request, HttpServletResponse response, DataGrid dataGrid)
	{
		String FDate_begin = request.getParameter("FDate_begin");
		String FDate_end = request.getParameter("FDate_end");
		
		String FName = request.getParameter("FItemName");
		String FCustName = request.getParameter("FName");
		String FModel_begin = request.getParameter("FModel_begin");
		String FModel_end = request.getParameter("FModel_end");
		
	String sql = " select t5.FName,Convert(decimal(18,4),sum(t2.FAuxQty)) as sum_qty,SUM(FAmount) as sum_amount,SUM(FAmount)/sum(t2.FAuxQty) as price ";
		sql += " FROM RemoteServer.AIS2009.dbo.ICSale t1 INNER JOIN RemoteServer.AIS2009.dbo.ICSaleEntry t2 ON t1.FInterID=t2.FInterID ";
		sql += " LEFT  JOIN RemoteServer.AIS2009.dbo.t_ICItem t3 ON t2.FItemID=t3.FItemID ";
		sql += " LEFT  JOIN RemoteServer.AIS2009.dbo.t_Item t5 ON t1.FCustID=t5.FItemID AND t5.FItemID<>0 ";
		sql += " where 1=1  ";
		//sql += "  and (  ";
		
		if (StringUtil.isNotEmpty(FDate_begin))
		{
			sql += " and t1.FDate >=  " + "'" + FDate_begin + "'";
		}
		if (StringUtil.isNotEmpty(FDate_end))
		{
			sql += " and t1.FDate <=  " + "'" + FDate_end + "'";
		}
		
		if (StringUtil.isNotEmpty(FModel_begin))
		{
			sql += " and t3.FModel >  " + "'" + FModel_begin + "'";
		}
		if (StringUtil.isNotEmpty(FModel_end))
		{
			sql += " and t3.FModel <=  " + "'" + FModel_end + "'";
		}
		
		if (StringUtil.isNotEmpty(FCustName))
		{
			sql += " and t5.FName like  " + "'%" + FCustName + "%'";
		}
		
	//	sql += "  t1.FDate >=  '2014-10-01'    AND ";
		//sql += "  t1.FDate <=  '2014-10-31' ";                
		
		
	//	sql += "  )  ";
		sql += " AND isnull(t1.FSaleStyle,0)=101  ";
		sql += " AND isnull(t1.FCheckerID,0)>0 "; 
		//sql += " and isnull(t1.FCancellation,0)=0  ";
		
		sql += " AND t1.FClassTypeID=1000002  AND ( t1.FSubSystemID>=0 and t1.finterid in(select finvoiceid from RemoteServer.AIS2009.dbo.t_rp_contact where ftype=3 and finvoiceid>0) and isnull(t1.FCancellation,0)=0 ) ";
		
		
		if (StringUtil.isNotEmpty(FName))
		{
			sql += " and t3.FName like  " + "'%" + FName + "%'";
		}
	//	sql += " and t3.FName like '%冷轧钢带%' ";
		sql += " group by t5.FName ";

		


	

		

/*		if(dataGrid.getSort() != null)
		{
			String orderField = dataGrid.getSort();
			if(dataGrid.getSort().equals(orderField)){
				if(dataGrid.getOrder() == SortDirection.asc){
					sql += " order by " + orderField + " asc";
				}else{
					sql += " order by " + orderField + " desc";
				}
			}
		}*/
        
		
		List<Map<String, Object>> maplist = null;
		List<Map<String, Object>> allmaplist = null;

		try
		{

			allmaplist = systemService.findForJdbc(sql);
			maplist = systemService.findForJdbcParam(sql, dataGrid.getPage(), dataGrid.getRows());

		} catch (Exception ex)
		{

		}
		dataGrid.setTotal(allmaplist.size());
		dataGrid.setResults(maplist);
		// 数量和金额合计
		//Double sum_qty = 0.00;
		BigDecimal sum_qty = new BigDecimal("0.0000");
		BigDecimal sum_amount = new BigDecimal("0.0000");
		for (Map<String, Object> map : maplist)
		{
			sum_qty = sum_qty.add((BigDecimal) map.get("sum_qty"));
			sum_amount = sum_amount.add((BigDecimal) map.get("sum_amount"));
		}
		dataGrid.setFooter("FName:合计," + "sum_qty:" + sum_qty + ",sum_amount:" + sum_amount);
		TagUtil.datagrid(response, dataGrid);
	}
	
	
	
	/**
	 * 
	 * 導出
	 * @param request
	 * @param response
	 * @param dataGrid
	 */
	@RequestMapping(params = "export_xsmx")
	public void export_xsmx(HttpServletRequest request,HttpServletResponse response, DataGrid dataGrid) {
		
		String FDate_begin = request.getParameter("FDate_begin");
		String FDate_end = request.getParameter("FDate_end");
		
		String FName = request.getParameter("FItemName");
		String FCustName = request.getParameter("FName");
		String FModel_begin = request.getParameter("FModel_begin");
		String FModel_end = request.getParameter("FModel_end");
		
	String sql = " select t5.FName,Convert(decimal(18,4),sum(t2.FAuxQty)) as sum_qty,SUM(FAmount) as sum_amount,SUM(FAmount)/sum(t2.FAuxQty) as price ";
		sql += " FROM RemoteServer.AIS2009.dbo.ICSale t1 INNER JOIN RemoteServer.AIS2009.dbo.ICSaleEntry t2 ON t1.FInterID=t2.FInterID ";
		sql += " LEFT  JOIN RemoteServer.AIS2009.dbo.t_ICItem t3 ON t2.FItemID=t3.FItemID ";
		sql += " LEFT  JOIN RemoteServer.AIS2009.dbo.t_Item t5 ON t1.FCustID=t5.FItemID AND t5.FItemID<>0 ";
		sql += " where 1=1  ";
		//sql += "  and (  ";
		
		if (StringUtil.isNotEmpty(FDate_begin))
		{
			sql += " and t1.FDate >=  " + "'" + FDate_begin + "'";
		}
		if (StringUtil.isNotEmpty(FDate_end))
		{
			sql += " and t1.FDate <=  " + "'" + FDate_end + "'";
		}
		
		if (StringUtil.isNotEmpty(FModel_begin))
		{
			sql += " and t3.FModel >  " + "'" + FModel_begin + "'";
		}
		if (StringUtil.isNotEmpty(FModel_end))
		{
			sql += " and t3.FModel <=  " + "'" + FModel_end + "'";
		}
		
		if (StringUtil.isNotEmpty(FCustName))
		{
			sql += " and t5.FName like  " + "'%" + FCustName + "%'";
		}
	//	sql += "  t1.FDate >=  '2014-10-01'    AND ";
		//sql += "  t1.FDate <=  '2014-10-31' ";                
		
		
	//	sql += "  )  ";
		sql += " AND isnull(t1.FSaleStyle,0)=101  ";
		sql += " AND isnull(t1.FCheckerID,0)>0 "; 
		//sql += " and isnull(t1.FCancellation,0)=0  ";
		
		sql += " AND t1.FClassTypeID=1000002  AND ( t1.FSubSystemID>=0 and t1.finterid in(select finvoiceid from RemoteServer.AIS2009.dbo.t_rp_contact where ftype=3 and finvoiceid>0) and isnull(t1.FCancellation,0)=0 ) ";
		
		
		if (StringUtil.isNotEmpty(FName))
		{
			sql += " and t3.FName like  " + "'%" + FName + "%'";
		}
	//	sql += " and t3.FName like '%冷轧钢带%' ";
		sql += " group by t5.FName ";
      
		List<Map<String, Object>> maplist = null;

		try
		{
			maplist = systemService.findForJdbc(sql);
			List<String[]> list = new ArrayList<String[]>();
			
			for (Map<String, Object> map : maplist) {
				String[] arr = new String[map.size()];
				arr[0] = (String) map.get("FName");
				arr[1] = String.valueOf((BigDecimal) map.get("sum_qty"));
				arr[2] = String.valueOf((BigDecimal) map.get("sum_amount"));
				list.add(arr);
			}
		
			BigDecimal sum_qty = new BigDecimal("0.0000");
			BigDecimal sum_amount = new BigDecimal("0.0000");
			for (Map<String, Object> map : maplist)
			{
				sum_qty = sum_qty.add((BigDecimal) map.get("sum_qty"));
				sum_amount = sum_amount.add((BigDecimal) map.get("sum_amount"));
			}
			String[] sums = new String[]{"合计",String.valueOf(sum_qty),String.valueOf(sum_amount)};
			list.add(sums);
			
			String[] columnNames = new String[] {"客户名称", "重量", "金额"};
	       String titleName = "销售明细表.xls";

			OutputStream fOut = response.getOutputStream();
			response.setHeader("Content-Disposition", "attachment; filename="
					+ new String(titleName.getBytes("GB2312"), "ISO8859-1"));

	        ExcelUtils.create(request, list, columnNames, titleName, fOut);
			
		} catch (Exception ex)
		{

		}
		
	}

}
